using System;
using System.Collections.Generic;
using System.Text;
using SecurityAccessLayer;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace DataAccessLayer
{
    public static class DAL
    {
        #region Private Member Variables 
        private static SqlConnection _sqlCon;
        private static bool _dalInitialized = false;
        private static SqlConnectionStringBuilder _sqlConSrtr;
        private static SqlCommand _checkValidUserByName= null;
        private static SqlCommand _checkValidUserByID = null;
        private static SqlCommand _getDoable = null;
        private static SqlCommand _cmdGetAllOpenJobs = null;
        private static SqlCommand _cmdGetAllFinalizedJobs = null;
        private static SqlCommand _cmdGetJobData = null;
        private static SqlCommand _cmdGetNotesByTaskID;
        private static SqlCommand _cmdGetPoByTaskID;
        private static SqlCommand _cmdGetLocationsByCustomerID;
        private static SqlCommand _cmdUpdateJob;

        private static SqlDataAdapter _daOpenJobList = null;
        private static SqlDataAdapter _daDoStuff = null;
        private static SqlDataAdapter _daGetJobData = null;
        private static SqlDataAdapter _daGetFinalizedJobs = null; 
        private static SqlDataAdapter _daGetNotesByTaskID = null;
        private static SqlDataAdapter _daGetPoByTaskID = null;
        private static SqlDataAdapter _daGetLocationsByCustomerID = null;
        private static SqlDataAdapter _daUpdateJob;
        

        #endregion

        #region Public Properties 

        public static bool Initialized
        {
            get { return _dalInitialized; }
        }

        #endregion

        #region Public Methods

        public static bool Initialize()
        {
            bool AllInitialized = false;
            bool initSqlConn = InitializeSQLConnection();
            if (initSqlConn)
            {
                // Continuing intializing DAL commands
                InitializeCommands();
                InitializeAdapters();
                AllInitialized = true;
            }
            return AllInitialized;
        }
              
        #region Create Methods
        /// <summary>
        /// Attempts to create user with userName and binds it to customerID, failing = -1
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="customerID"></param>
        /// <returns></returns>
        public static int CreateUser(string userName, int customerID)
        {
            //TODO: Check for Valid username, customerID.
            return -1;
        }

        /// <summary>
        /// Attempts to create dostuff permissions. If any duplicates returns true.
        /// </summary>
        /// <param name="dostuffName"></param>
        /// <param name="dostuffDescription"></param>
        /// <returns></returns>
        public static bool CreateDoStuff(string dostuffName, string dostuffDescription)
        {
            //TODO: check for duplicate names then returns true.
            return false;
        }

        /// <summary>
        /// Attempts to create task prior to assigning to a consultant
        /// </summary>
        /// <param name="JobID"></param>
        /// <param name="LocationID"></param>
        /// <param name="Description"></param>
        /// <returns></returns>
        public static int CreateTaskPreAssignment(int jobID, int locationID, string taskDescription)
        {
            //TODO: Creates task assigning JobID, LocationID, and brief description.
            return -1;
        }

        /// <summary>
        /// Quickly input information while speaking with customer
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="locationID"></param>
        /// <param name="Description"></param>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <param name="Addressed"></param>
        /// <param name="Status"></param>
        /// <returns></returns
        public static int CreateTaskonSite(int jobID, int locationID, string Description, DateTime StartTime, DateTime EndTime, bool Addressed, string Status)
        {
            //TODO: Inputs data concerning work done on job and tasks.
            return -1;
        }

        /// <summary>
        /// Information concerning purchases or taking parts out of inventory.
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="description"></param>
        /// <param name="manufacturer"></param>
        /// <param name="modelNumber"></param>
        /// <param name="quantity"></param>
        /// <param name="price"></param>
        /// <returns></returns>
        public static int CreatePO(int taskID, string description, string manufacturer, string modelNumber, int quantity, int price)
        {
            //TODO: Inputs data to keep track of inventory or parts purchased.
            return -1;
        }

        /// <summary>
        /// Information technicians inputs concerning tasks.
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="userID"></param>
        /// <param name="description"></param>
        /// <returns></returns>
        public static int CreateNote(int taskID, int userID, string description)
        {
            //TODO: creates history of notes sorted by tasks.
            return -1;
        }
            
        /// <summary>
        /// Location information bound to customers
        /// </summary>
        /// <param name="customerID"></param>
        /// <param name="locationName"></param>
        /// <param name="address1"></param>
        /// <param name="address2"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="zip"></param>
        /// <param name="purposeID"></param>
        /// <returns></returns>
        public static int Location(int customerID, string locationName, string address1, string address2, string city, string state, string zip, int purposeID)
        {
            //TODO:create locations based on customer.
            return -1;
        }

        /// <summary>
        /// Job information bound to customers and billingmethod.
        /// </summary>
        /// <param name="customerID"></param>
        /// <param name="description"></param>
        /// <param name="billingmethodID"></param>
        /// <returns></returns>
        public static int CreateJob(int customerID, string description, int billingmethodID)
        {
            //TODO: Creates jobs based on customer.
            return -1;
        }

        /// <summary>
        /// Assigns jobs to consultants.
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static int AssignUserToJob (int jobID, int userID)
        {
            //TODO: Method to assign created job to a user.
            return -1;
        }

        /// <summary>
        /// Method to bill customers based on job done by consultants.
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="description"></param>
        /// <returns></returns>
        public static int CreateInvoice(int jobID, string description)
        {
            //TODO: creates invoices to bill customers.
            return -1;
        }

        /// <summary>
        /// Create invoice purchase order method.
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <param name="description"></param>
        /// <param name="price"></param>
        /// <returns></returns>
        public static int CreateInvoicePO(int invoiceID, string description, int price)
        {
            //TODO: create a purchase order invoice to bill customers on parts used.
            return -1;
        }

        /// <summary>
        /// Create invoice task method to bill customers by tasks.
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <param name="description"></param>
        /// <param name="timeBilled"></param>
        /// <returns></returns>
        public static int CreateInvoiceTask(int invoiceID, string description, int timeBilled)
        {
            //TODO: create an invoice for tasks in a job.
            return -1;
        }

        /// <summary>
        /// Method to create customers.
        /// </summary>
        /// <param name="customerName"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        /// <returns></returns>
        public static int CreateCustomer(string customerName, string phone, string fax)
        {
            //TODO: creates customers.
            return 1;
        }

        /// <summary>
        /// Method to create contacts for customers.
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <param name="officePhone"></param>
        /// <param name="mobilePhone"></param>
        /// <param name="email"></param>
        /// <param name="worklocationID"></param>
        /// <param name="customerID"></param>
        /// <param name="purposetypeID"></param>
        /// <param name="purchasedescription"></param>
        /// <returns></returns>
        public static int CreateContact(string firstName, string lastName, string officePhone, string mobilePhone, string email, int worklocationID, int customerID, int purposetypeID, string purchasedescription)
        {
            //TODO: creates contacts for customers
            return -1;
        }

        #endregion

        #region Updating Methods

        public static bool UpdateJob(int userID, DataRow job)
        {
            bool success = false;
            if (SAL.IsValidatedUser(userID) == ValidState.Valid && SAL.CanUserDoStuff(userID, DoStuffType.Manager))
            {
                DAL.spUpdateJob(job);
            }
            return success;
        }

        #endregion Updating Methods

        #region Query Methods

        public static ValidState IsUserValid(string userName)
        {
            return spIsUserValid(userName);

        }

        public static object GetPermissionsByUserID(int userID)
        {
            return new object();
        }

        public static int GetUserId(string userName)
        {
            return 1;

        } 
        
        public static DataTable GetAllOpenJobs(int userID)
        {
            DataTable dtOpenJobs = null;

            // TODO:Check if user is valid 
            if (SAL.IsValidatedUser(userID) == ValidState.Valid)
            // TODO:Check if user can perform this action
            {
                if (SAL.CanUserDoStuff(userID, DoStuffType.Manager))
                {
                    dtOpenJobs = spGetNonFinalizedJobs();
                }
                else 
                {
                    dtOpenJobs = null;
                }
            }
            else
            {
                dtOpenJobs = null; 
            }
            return dtOpenJobs;
        }

        public static DataTable GetAllFinalizedJobs(int userID)
        {
            DataTable dtFinalizedJobs = null;

            // TODO:Check if user is valid 
            if (SAL.IsValidatedUser(userID) == ValidState.Valid)
            // TODO:Check if user can perform this action
            {
                if (SAL.CanUserDoStuff(userID, DoStuffType.Manager))
                {
                    dtFinalizedJobs = spGetFinalizedJobs();
                }
                else
                {
                    dtFinalizedJobs = null;
                }
            }
            else
            {
                dtFinalizedJobs = null;
            }
            return dtFinalizedJobs;
        }

        public static ValidState IsUserValid(int userID)
        {
            return spIsUserValid(userID);
        }

        public static bool CanUserDoStuff(int userID, DoStuffType doStuff)
        {
            return spCanUserDoStuff(userID, doStuff);
        }

        public static bool GetJobData(int userID,int jobID, out DataSet jobData)
        {
            bool success = false;
            jobData = null;
            if (SAL.IsValidatedUser(userID) == ValidState.Valid && SAL.CanUserDoStuff(userID, DoStuffType.Manager))
            {
                jobData = spGetJobData(jobID);
                if (jobData != null)
                {
                    success = true;
                }
            }   
            return success;
        }

        public static bool GetLocationsByCustomerID(int userID, int customerID, out DataTable locations)
        {
            bool success = false;
            locations = null;
            if (SAL.IsValidatedUser(userID) == ValidState.Valid && SAL.CanUserDoStuff(userID, DoStuffType.Consultant))
            {
                locations = spGetLocationsByCustomerID(customerID);
                success = true;
            }
            else
            {
                success = false;
            }
            return success;
        }

        public static bool GetPurchaseOrdersByTaskID(int userID, int taskID, out DataTable purchaseOrders)
        {
            bool success = false;
            purchaseOrders = null;
            if (SAL.IsValidatedUser(userID) == ValidState.Valid && SAL.CanUserDoStuff(userID, DoStuffType.Consultant))
            {
                purchaseOrders = spGetPuchaseOrdersByTaskID(taskID);
            }
            return success;
        }

        #endregion //End public Query Methods
        
        #endregion Public Methods
               
        #region Private Methods

        #region Private Helper Methods

        private static bool InitializeSQLConnection()
        {
            _dalInitialized = false;

            // EJR Initial Catalog and DataSource need moving to configurations - app.config
            _sqlConSrtr = new SqlConnectionStringBuilder();
            _sqlConSrtr.InitialCatalog = "ConsultTracking";
            _sqlConSrtr.IntegratedSecurity = true;
            _sqlConSrtr.DataSource = @".\SQLEXPRESS";
            _sqlCon = new SqlConnection(_sqlConSrtr.ConnectionString);

            try
            {
                _sqlCon.Open();
                _dalInitialized = true;
            }
            catch (Exception ex)
            {


            }
            finally
            {

            }
            return _dalInitialized;
        }

        private static SqlCommand CreateSqlCommand(string cmdText, SqlConnection sqlCon, params SqlParameter[] sqlParams)
        {
            SqlCommand sqlCmd = null;
            if (_dalInitialized)
            {
                sqlCmd = new SqlCommand(cmdText, sqlCon);
                if (sqlParams != null)
                {
                    sqlCmd.Parameters.AddRange(sqlParams);
                }
            }
            else
            {
                throw new Exception("DAL:Cannot create Sql Commands until SQL Initialized - " + cmdText);
            }
            return sqlCmd;
        }

        private static void InitializeCommands()
        {
            // CheckValidUserByName
            SqlParameter[] cvubnParams= new SqlParameter[2];
            cvubnParams[0]= new SqlParameter(Keys.userLogOnName, SqlDbType.NVarChar);
            cvubnParams[0].Direction = ParameterDirection.Input;
            cvubnParams[1]= new SqlParameter(Keys.userActive, SqlDbType.Bit);
            cvubnParams[1].Direction = ParameterDirection.Output;
            _checkValidUserByName = CreateSqlCommand("select @Active = Active from [User] where LogOnName = @UserName",
                                               _sqlCon,
                                               cvubnParams);

            // CheckValidUserByID
            SqlParameter[] cvubiParams = new SqlParameter[2];
            cvubiParams[0] = new SqlParameter(Keys.userUserID, SqlDbType.NVarChar);
            cvubiParams[0].Direction = ParameterDirection.Input;
            cvubiParams[1] = new SqlParameter(Keys.userActive, SqlDbType.Bit);
            cvubiParams[1].Direction = ParameterDirection.Output;
            _checkValidUserByID = CreateSqlCommand("select @Active = Active from [User] where UserID = @UserID",
                                               _sqlCon,
                                               cvubiParams);
            // CanUserDoStuff
            SqlParameter[] cudsParams = new SqlParameter[3];
            cudsParams[0] = new SqlParameter(Keys.userUserID, SqlDbType.Int);
            cudsParams[0].Direction = ParameterDirection.Input;
            cudsParams[1] = new SqlParameter(Keys.dostuffDoStuffID, SqlDbType.Int);
            cudsParams[1].Direction = ParameterDirection.Input;
            cudsParams[2] = new SqlParameter(Keys.dostuffUserCanDoStuff, SqlDbType.Bit);
            cudsParams[2].Direction = ParameterDirection.Output;
            _getDoable = CreateSqlCommand("EXECUTE CanUserDoStuff " + Keys.userUserID +
                                                                "," + Keys.dostuffDoStuffID +
                                                                "," + Keys.dostuffUserCanDoStuff + " out", _sqlCon, cudsParams);
            // GetAllOpenJobs 
            _cmdGetAllOpenJobs = CreateSqlCommand("EXECUTE GetNonFinalizedJobs",_sqlCon,null);

            // GetAllFinalizedJobs
            _cmdGetAllFinalizedJobs = CreateSqlCommand("SELECT * FROM Jobs WHERE Finalized = 'True'", _sqlCon, null);

            //
            SqlParameter[] gjdParams = new SqlParameter[1];
            gjdParams[0] = new SqlParameter(Keys.Params.jobJobID, SqlDbType.Int);
            gjdParams[0].Direction= ParameterDirection.Input;
            _cmdGetJobData = CreateSqlCommand("EXECUTE GetJobInfoByJobID " + Keys.Params.jobJobID, _sqlCon, gjdParams);

            // GetLocationByID
            SqlParameter[] glbidParams = new SqlParameter[1];
            glbidParams[0] = new SqlParameter(Keys.Params.locationCustomerID, SqlDbType.Int);
            glbidParams[0].Direction = ParameterDirection.Input;
            _cmdGetLocationsByCustomerID = CreateSqlCommand("EXECUTE GetLocationsByCustomerID " + Keys.Params.locationCustomerID, _sqlCon, glbidParams);

            // GetPoByTaskID
            SqlParameter[] gpobtidParams = new SqlParameter[1];
            gpobtidParams[0] = new SqlParameter(Keys.Params.purchaseorderTaskID, SqlDbType.Int);
            _cmdGetPoByTaskID = CreateSqlCommand("Execute GetPurchaseOrdersByTaskID " + Keys.Params.purchaseorderTaskID, _sqlCon, gpobtidParams);

            SqlParameter[] updateJobParams = new SqlParameter[5];
            updateJobParams[0] = new SqlParameter(Keys.Params.jobJobID, SqlDbType.Int,0,"JobID");
            updateJobParams[1] = new SqlParameter(Keys.Params.jobDescription, SqlDbType.NVarChar, 100, "Description");
            updateJobParams[2] = new SqlParameter(Keys.Params.jobBillingID, SqlDbType.Int,0,"BillingMethodID");
            updateJobParams[3] = new SqlParameter(Keys.Params.jobFinalized, SqlDbType.Bit,0,"Finalized");
            updateJobParams[4] = new SqlParameter(Keys.Params.jobModified, SqlDbType.SmallDateTime,0,"Modified");
            _cmdUpdateJob = CreateSqlCommand("EXECUTE UpdateJob @JobID,@Description,@BillingMethod,@Finalized,@Modified",
                _sqlCon, updateJobParams);

                
        }

        private static void InitializeAdapters()
        {
            // OpenJobList Adapter uses _cmdGetAllOpenJobs
            _daOpenJobList = new SqlDataAdapter("",_sqlCon);
            _daOpenJobList.SelectCommand = _cmdGetAllOpenJobs;

            // GetJobData Adapter uses _cmdGetJobData
            _daGetJobData = new SqlDataAdapter("", _sqlCon);
            _daGetJobData.SelectCommand = _cmdGetJobData;
            _daGetJobData.TableMappings.Add("Table", "Job");
            _daGetJobData.TableMappings.Add("Table1", "User");
            _daGetJobData.TableMappings.Add("Table2", "Task");

            //
            _daGetLocationsByCustomerID = new SqlDataAdapter("", _sqlCon);
            _daGetLocationsByCustomerID.SelectCommand = _cmdGetLocationsByCustomerID;

            //
            _daGetPoByTaskID = new SqlDataAdapter("", _sqlCon);
            _daGetPoByTaskID.SelectCommand = _cmdGetPoByTaskID;

            //Update Job
            _daUpdateJob = new SqlDataAdapter("", _sqlCon);
            _daUpdateJob.UpdateCommand = _cmdUpdateJob;

        }
             
        #endregion // end Private Helper Methods

        #region Private Create Methods
        /// <summary>
        /// Calls stored procedure to Create a new user with userName and related customerID.
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="customerID"></param>
        private static void spCreateUser(string userName, int customerID)
        {
            // TODO: Call sproc CreateUser 
        }

        /// <summary>
        /// Calls stored procedure to Create a dostuff (user right) with dostuffName and dostuffDescription.
        /// </summary>
        /// <param name="dostuffName"></param>
        /// <param name="dostuffDescription"></param>
        private static void spCreateDoStuff(string dostuffName, string dostuffDescription)
        {
            // TODO: Call sproc CreateDoStuff
        }

        /// <summary>
        /// Calls stored procedure CreateTaskPreAssignment with jobID, locationID, and taskDescription.
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="locationID"></param>
        /// <param name="taskDescription"></param>
        private static void spCreateTaskPreAssignment(int jobID, int locationID, string taskDescription)
        {
            // TODO: Call sproc CreateTaskPreAssignment
        }

        /// <summary>
        /// Calls stored procedure CreateTaskOnSite with ...
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="locationID"></param>
        /// <param name="taskDescription"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="addressed"></param>
        /// <param name="status"></param>
        private static void spCreateTaskOnSite(int jobID, int locationID, string taskDescription,
                                                DateTime startTime, DateTime endTime, bool addressed, string status)
        {
            // TODO: Call sproc CreateTaskOnSite
        }

        /// <summary>
        /// Calls stored procedure CreatePO with ...
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="taskDescription"></param>
        /// <param name="manufacturer"></param>
        /// <param name="modelNumber"></param>
        /// <param name="quantity"></param>
        /// <param name="price"></param>
        private static void spCreatePO(int taskID, string taskDescription, string manufacturer, string modelNumber, int quantity, double price)
        {
            // TODO: Create a purchase order with new information
        }

        /// <summary>
        /// Calls stored procedure with taskID, userID, and noteDescription.
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="userID"></param>
        /// <param name="noteDescription"></param>
        private static void spCreateNote(int taskID, int userID, string noteDescription)
        {
            // TODO: Create a note that belongs to a particular task made by a user.
        }

        /// <summary>
        /// Calls stored procedure CreateLoction with ...
        /// </summary>
        /// <param name="customerID"></param>
        /// <param name="locationName"></param>
        /// <param name="address1"></param>
        /// <param name="address2"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="zip"></param>
        /// <param name="purposeTypeID"></param>
        private static void spCreateLocation(int customerID, string locationName, string address1, string address2,
                                             string city, string state, string zip, int purposeTypeID)
        {
            // TODO: Create a new location with information and a particular purpose type
        }

        /// <summary>
        /// Calls stored procedure CreateJob with a customerID, job desciption, and billing method.
        /// </summary>
        /// <param name="customerID"></param>
        /// <param name="jobDescription"></param>
        /// <param name="billingMethodID"></param>
        private static void spCreateJob(int customerID, string jobDescription, int billingMethodID)
        {
            // TODO: Create a new job with a description and billing method
        }
     
        /// <summary>
        /// Calls stored procedure AssignUserToJob with a jobID and a userID.
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="userID"></param>
        private static void spAssignUserToJob(int jobID, int userID)
        {
            //TODO: Assign a user to a job so they can update information
            // Does not check to see if user already assigned to job - EJR where does that go?
        }

        /// <summary>
        /// Calls stored procedure CreateInvoice with a jobID and a invoice description.
        /// </summary>
        /// <param name="jobID"></param>
        /// <param name="invoiceDescription"></param>
        private static void spCreateInvoice(int jobID, string invoiceDescription)
        {
            // TODO: Create a new invoice for a job with a description
        }
  
        /// <summary>
        /// Calls stored procedure CreateInvoicePO with an invoiceID, description, and a price.
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <param name="invoicePODescription"></param>
        /// <param name="price"></param>
        private static void spCreateInvoicePO(int invoiceID, string invoicePODescription, double price)
        {
            // TODO: Create a new invoice PO line item for invoiceID, a description, and a price
        }
      
        /// <summary>
        /// Calls stored procedure CreateInvoiceTask with an invoiceID, description, and a time billed.
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <param name="invoiceTaskDescription"></param>
        /// <param name="timeBilled"></param>
        private static void spCreateInvoiceTask(int invoiceID, string invoiceTaskDescription, double timeBilled)
        {
            // TODO: Create a invoive task line item for invoiceID, a description, and a time billed value
        }

        /// <summary>
        /// Calls stored procedure CreateCustomer with name, phone, and fax information
        /// </summary>
        /// <param name="customerName"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        private static void spCreateCustomer(string customerName, string phone, string fax)
        {
            // TODO: Create a new customer with a name, phone, and fax number
        }
       
        /// <summary>
        /// Calls stored procedure CreateContact with ...
        /// </summary>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <param name="officePhone"></param>
        /// <param name="mobilePhone"></param>
        /// <param name="email"></param>
        /// <param name="workLocationID"></param>
        /// <param name="customerID"></param>
        /// <param name="purposeTypeID"></param>
        /// <param name="purposeDescription"></param>
        private static void spCreateContact(string firstName, string lastName, string officePhone, string mobilePhone,
                                            string email, int workLocationID, int customerID, int purposeTypeID,
                                            string purposeDescription)
        {
            // TODO: Create a new contact with new information and a purpose
        }

        #endregion // end Private Create Methods

        #region Private Update Methods 
        /// <summary>
        /// Calls stored procedure UpdateUser with ...
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="userName"></param>
        /// <param name="customerID"></param>
        private static void spUpdateUser(int userID, string userName,int customerID)
        {
            // TODO: Update users logon name and/or this user belongs to a customer of customerID
        }

        /// <summary>
        /// Calls stored procedure SetUserDoStuff with ...
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="doStuffID"></param>
        /// <param name="doable"></param>
        private static void spSetUserDoStuff(int userID, int doStuffID, bool doable)
        {
            // TODO: Update user DoStuff action
        }

        /// <summary>
        /// Calls stored procedure UpdateTask with ...
        /// </summary>
        /// <param name="taskID"></param>
        /// <param name="taskDescription"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="addressed"></param>
        /// <param name="status"></param>
        private static void spUpdateTask(int taskID, string taskDescription, DateTime startTime, DateTime endTime, bool addressed, string status)
        {
            // TODO: Update task information description, start and end times, task addressed, status
        }

        /// <summary>
        /// Calls stored procedure UpdatePO with ...
        /// </summary>
        /// <param name="poID"></param>
        /// <param name="descriptionID"></param>
        /// <param name="manufacturer"></param>
        /// <param name="modelNumber"></param>
        /// <param name="quantity"></param>
        /// <param name="price"></param>
        private static void spUpdatePO(int poID, int descriptionID, string manufacturer, string modelNumber, int quantity, double price)
        {
            //TODO: Update purchase order information description, manufacturer, model number, quantity, price
        }

        /// <summary>
        /// Calls stored procedure with noteID, userID, and noteDescription
        /// </summary>
        /// <param name="noteID"></param>
        /// <param name="userID"></param>
        /// <param name="noteDescription"></param>
        private static void spUpdateNote(int noteID, int userID, string noteDescription)
        {
            // TODO: Update a note by a userID and a new note description
        }

        /// <summary>
        /// Calls stored procedure UpdateLocation with ...
        /// </summary>
        /// <param name="locationID"></param>
        /// <param name="locationName"></param>
        /// <param name="address1"></param>
        /// <param name="address2"></param>
        /// <param name="city"></param>
        /// <param name="state"></param>
        /// <param name="zip"></param>
        private static void spUpdateLocation(int locationID, string locationName, string address1, string address2,
                                             string city, string state, string zip)
        {
            // TODO: Update a location by locationID and new information
        }

        private static void spUpdateJob(DataRow job)
        {
            int i = _daUpdateJob.Update(new DataRow[] {job});
        }
  
        /// <summary>
        /// Calls stored procedure UpdateInvoice with an invoiceID and a new description
        /// </summary>
        /// <param name="invoiceID"></param>
        /// <param name="invoiceDescription"></param>
        private static void spUpdateInvoice(int invoiceID, string invoiceDescription)
        {
            // TODO: Update invoice description
        }

        /// <summary>
        /// Calls stored procedure UpdateInvoicePO with an invoicePOID and new descrtiption and price.
        /// </summary>
        /// <param name="invoicePOID"></param>
        /// <param name="invoicePODescription"></param>
        /// <param name="price"></param>
        private static void spUpdateInvoicePO(int invoicePOID, string invoicePODescription, double price)
        {
            // TODO: Update invoice PO line item description and price
        }
        
        /// <summary>
        /// Calls stored procedure UpdateInvoiceTask with an invoiceTaskID, a description, and time billed
        /// </summary>
        /// <param name="invoiceTaskID"></param>
        /// <param name="invoiceTaskDescription"></param>
        /// <param name="timeBilled"></param>
        private static void spUpdateInvoiceTask(int invoiceTaskID, string invoiceTaskDescription, double timeBilled)
        {
            // TODO: Update invoice task line item description and time billed
        }

        /// <summary>
        /// Calls stored procedure UpdateCustomer with a new name, phone, fax, and active status
        /// </summary>
        /// <param name="customerID"></param>
        /// <param name="customerName"></param>
        /// <param name="phone"></param>
        /// <param name="fax"></param>
        /// <param name="active"></param>
        private static void spUpdateCustomer(int customerID, string customerName, string phone, string fax, bool active)
        {
            // TODO: Update a customers name, phone, fax, and whether they are active
        }

        /// <summary>
        /// Calls stored procedure UpdateContact with ...
        /// </summary>
        /// <param name="contactID"></param>
        /// <param name="firstName"></param>
        /// <param name="lastName"></param>
        /// <param name="officePhone"></param>
        /// <param name="mobilePhone"></param>
        /// <param name="email"></param>
        /// <param name="workLocationID"></param>
        private static void spUpdateContact(int contactID, string firstName, string lastName, string officePhone,
                                            string mobilePhone, string email, int workLocationID)
        {
            // TODO: Update a contact with first name, last name, office phone, mobile phone, email, workLocationID
        }
       
        #endregion // end Private Update Methods

        #region Private Delete Methods 

        #endregion // end Private Delete Methods

        #region Private Query Methods 

        /// <summary>
        /// Calls stored procedure GetNonFinalizedJobs with no parameters.
        /// </summary>
        /// <returns></returns>
        private static DataTable spGetNonFinalizedJobs()
        {
            DataTable openJobs = new DataTable();
            _daOpenJobList.Fill(openJobs);
            return openJobs;
        }

        private static DataTable spGetFinalizedJobs()
        {
            DataTable finalizedJobs = new DataTable();
            _daGetFinalizedJobs.Fill(finalizedJobs);
            return finalizedJobs;
        
        }
        
        /// <summary>
        /// returns true if user exists and is active
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        private static ValidState spIsUserValid(string userName)
        {
            ValidState status = ValidState.Undefined;
            _checkValidUserByName.Parameters[Keys.userLogOnName].Value = userName;
            _checkValidUserByName.ExecuteNonQuery();
            if (status == ValidState.Undefined)
            {
                try
                {
                    if ((bool)_checkValidUserByName.Parameters[Keys.userActive].Value)
                    {
                        status = ValidState.Valid;
                    }
                    else
                    {
                        status = ValidState.NotValid;
                    }
                }
                catch (Exception)
                {
                    status = ValidState.DoesNotExist;
                }
            }
            return status;
        }

        /// <summary>
        /// Returns a Valid State representing if a user is valid
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        private static ValidState spIsUserValid(int userID)
        {
            ValidState status = ValidState.Undefined;
            _checkValidUserByID.Parameters[0].Value = userID;
            _checkValidUserByID.ExecuteNonQuery();
            if (status == ValidState.Undefined)
            {
                try
                {
                    if ((bool)_checkValidUserByID.Parameters[Keys.userActive].Value)
                    {
                        status = ValidState.Valid;
                    }
                    else
                    {
                        status = ValidState.NotValid;
                    }
                }
                catch (Exception)
                {
                    status = ValidState.DoesNotExist;
                }
            }
            return status;
        }

        /// <summary>
        /// Returns a UserID based on a User's Logon Name
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        private static int spGetUserID(string userName)
        {
            return 1;
        }

        /// <summary>
        /// Returns a boolean representing wheather a User is Authorized to do take some action
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="doStuff"></param>
        /// <returns></returns>
        private static bool spCanUserDoStuff(int userID, DoStuffType doStuff)
        {
            _getDoable.Parameters[Keys.userUserID].Value = userID;
            _getDoable.Parameters[Keys.dostuffDoStuffID].Value = (int)doStuff;
            _getDoable.ExecuteScalar();
            return (bool)_getDoable.Parameters[Keys.dostuffUserCanDoStuff].Value;
        }

        /// <summary>
        /// Get a dataset containing information from the Job table and 
        /// information from the related records in the User and Task Tables
        /// </summary>
        /// <param name="JobID"></param>
        /// <returns></returns>
        private static DataSet spGetJobData(int JobID)
        {
            DataSet ds = new DataSet("JobData");
            _daGetJobData.SelectCommand.Parameters[Keys.Params.jobJobID].Value = JobID;
            try
            {
                _daGetJobData.Fill(ds);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds;
        }

        private static DataTable spGetLocationsByCustomerID(int customerID)
        {
            DataTable dt = new DataTable();
            _daGetLocationsByCustomerID.SelectCommand.Parameters[Keys.Params.locationCustomerID].Value = customerID;
            try
            {
                _daGetLocationsByCustomerID.Fill(dt);
            }
            catch (Exception Ex)
            {
                dt = null;
            }
            return dt;
        }

        private static DataTable spGetPuchaseOrdersByTaskID(int taskID)
        {
            DataTable dt = new DataTable("PurchaseOrders");
            _daGetPoByTaskID.SelectCommand.Parameters[Keys.Params.purchaseorderTaskID].Value = taskID;
            try
            {
                _daGetPoByTaskID.Fill(dt);
            }
            catch (Exception ex)
            {
                dt = null;
            }
            return dt;
        }

        #endregion  // end Query Methods

        #endregion // end Private Methods

    }
}
